package dao.impl;

import dao.HouseholdDao;
import entity.Household;
import utils.DBconn;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author pengrui
 * @version 1.0
 * @date 2021/9/8 18:27
 * @Description 住户管理dao实现类
 */
public class HouseholdDaoImpl implements HouseholdDao {
    @Override
    public boolean select(Household household) {
        boolean flag=false;
        try {
            DBconn.init();
            ResultSet resultSet = DBconn.selectSql("select * from household where name='"+household.getName()+"'");
            while(resultSet.next()){
                if(resultSet.getString("name").equals(household.getName())){
                    flag = true;
                }
            }
            DBconn.closeConn();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return flag;
    }

    @Override
    public boolean insert(Household household) {
        boolean flag = false;
        DBconn.init();
        int i = DBconn.addUpdDel("insert into household(name,sex,phoneNumber,company,Community,location)"
                + "value('" + household.getName() + "','" + household.getSex() + "','" + household.getPhoneNumber()
                + "','" + household.getCompany() + "','" + household.getCommunity() + "','" + household.getLocation()
                + "')");
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();

        return flag;
    }

    @Override
    public boolean delete(String name) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from household where id=" + name;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean update(Household household) {
        boolean flag = false;
        DBconn.init();
        String sql = "update household set sex='" + household.getSex()
                + "',phoneNumber='" + household.getPhoneNumber()
                + "',company='" + household.getCompany()
                + "',Community='" + household.getCommunity()
                + "',location='" + household.getLocation()
                + "',community='" + household.getCommunity()
                + "',location='" + household.getLocation()
                + "' where name='" + household.getName()+"'";

        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        return flag;
    }


    public List<Household> getAllHousehold(int page, int limit) {
        List<Household> list = new ArrayList<Household>();
        try {
            DBconn.init();
            ResultSet resultSet = DBconn.selectSql("select * from household limit " + (page - 1) * limit + "," + limit + " ");
            while (resultSet.next()) {
                Household household = new Household();
                household.setId(resultSet.getInt("id"));
                household.setName(resultSet.getString("name"));
                household.setSex(resultSet.getString("sex"));
                household.setPhoneNumber(resultSet.getString("phoneNumber"));
                household.setCompany(resultSet.getString("company"));
                household.setCommunity(resultSet.getString("Community"));
                household.setLocation(resultSet.getString("location"));
                list.add(household);
            }
            DBconn.closeConn();
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public int getCountByHousehold() {
        int count = 0;
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select count(*) from household");
            if (rs.next()) {
                count = rs.getInt(1);

            }
            DBconn.closeConn();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public boolean deleteById(List list) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete  from household where id=" + list.get(0);

        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    public boolean deleteByIds(List list) {

        boolean flag = false;
        DBconn.init();
        StringBuilder sql = new StringBuilder("delete  from household where id in( ");
        for (int i = 0; i < list.size(); i++) {
            sql.append("" + list.get(i) + ",");
        }

        sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, ")");

        int i = DBconn.addUpdDel(sql.toString());
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }





}
